"""
Case Type   : GUC
Case Name   : 主机执行100 unique_sql,备机执行1，查询记录条数
Create At   : 2021/05/08
Owner       : opentestcase018
Description :
    1、查询参数默认值
    show enable_auto_clean_unique_sql;
    show enable_resource_track;
    show instr_unique_sql_count;
    2、修改enable_auto_clean_unique_sql为on，重启使其生效，并校验其预期结果；
    gs_guc set -N all -I all -c "enable_auto_clean_unique_sql=on"
    gs_om -t stop && gs_om -t start
    3、清空记录，主机执行94+2 unique_sql，加上数据库内部执行的语句，备机执行1
     不触发自动淘汰，查看hash table记录条数
    select reset_unique_sql('GLOBAL','ALL',100);
    select count(va) from (select get_instr_unique_sql() as va);
    gsql -r -d -p -f test.sql
    select count(va) from (select get_instr_unique_sql() as va);
    4、恢复默认值；
    gs_guc set -N all -I all -c "enable_auto_clean_unique_sql=off"
    gs_om -t stop && gs_om -t start
Expect      :
    1、显示默认值
    2、参数修改成功，校验修改后系统参数
    3、主机查看hash table记录条数大于等于96，小于等于100，备机查看小于等于89
    4、恢复默认值成功；
History     :
    modified by li-xin12345 at 2025-1-21：适配池化
"""

import os
import unittest
import time

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node

LOGGER = Logger()
COMMONSH = CommonSH("PrimaryDbUser")


@unittest.skipIf(1 == COMMONSH.get_node_num(),
                 'Single node, and subsequent codes are not executed.')
class Guctestcase(unittest.TestCase):
    def setUp(self):
        LOGGER.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.com_s1 = CommonSH("Standby1DbUser")
        self.userNode = Node('dbuser')
        status = COMMONSH.get_db_cluster_status("detail")
        LOGGER.info(status)
        self.assertTrue("Normal" in status or "Degraded" in status)
        status = COMMONSH.restart_db_cluster()
        LOGGER.info(status)
        status = COMMONSH.get_db_cluster_status("detail")
        LOGGER.info(status)
        self.assertTrue("Normal" in status or "Degraded" in status)

    def set_gs_guc(self, param, value, method="set"):
        sql_cmd = COMMONSH.execut_db_sql(f"show {param}")
        LOGGER.info(sql_cmd)
        if f"{value}" != sql_cmd.splitlines()[-2].strip():
            COMMONSH.execute_gsguc(method,
                                   self.constant.GSGUC_SUCCESS_MSG,
                                   f"{param}={value}")
        LOGGER.info(f"{param} is {value}")

    def test_unique_sql(self):
        LOGGER.info("步骤1+2：核对参数默认值 并修改参数")
        LOGGER.info("主机设置")
        self.set_gs_guc("enable_auto_clean_unique_sql", "yes")
        status = COMMONSH.restart_db_cluster()
        LOGGER.info(status)
        self.set_gs_guc("use_workload_manager", "on", "reload")
        self.set_gs_guc("enable_resource_track", "on", "reload")
        self.set_gs_guc("instr_unique_sql_count", "100", "reload")
        sql_cmd = COMMONSH.execut_db_sql("show enable_resource_track;"
                                         "show use_workload_manager;"
                                         "show instr_unique_sql_count;"
                                         "show enable_auto_clean_unique_sql;")
        LOGGER.info(sql_cmd)
        self.assertNotIn("off", sql_cmd)
        self.assertIn("on", sql_cmd)
        self.assertIn("100", sql_cmd)

        LOGGER.info("步骤3：清空记录，主机执行94+2 unique_sql，"
                    "加上数据库内部执行的语句(centos和openEuler自动执行条数不一致)"
                    "备机执行1不触发自动淘汰，查看hash table记录条数")
        com_list = [COMMONSH, self.com_s1]
        for com in com_list:
            result = com.execut_db_sql("select "
                "reset_unique_sql('GLOBAL','ALL',100);"
                "select count(*) from dbe_perf.statement;")
            LOGGER.info(result)
            self.assertIn("t\n", result)
            self.assertIn("1\n", result)
        res = self.userNode.sh('cat /etc/system-release').result()
        LOGGER.info(res)
        if 'openEuler release' in res:
            count = 46
        else:
            count = 45
        for j in range(count):
            tablename = f"unique_table_{j}"
            result = COMMONSH.execut_db_sql(f"drop table if exists "
                                            f"{tablename};"
                                            f"create table {tablename}"
                                            f"(a int, b int);"
                                            f"drop table if exists "
                                            f"{tablename};")
            LOGGER.info(result)
            self.assertNotIn("ERROR", result)
        sql_detail = COMMONSH.execut_db_sql("select user_id,"
                                            "unique_sql_id,"
                                            "query from "
                                            "dbe_perf.statement;")
        LOGGER.info(sql_detail)
        result = COMMONSH.execut_db_sql("select count(*) "
                                        "from dbe_perf.statement;")
        LOGGER.info(result)
        new_res = int(result.splitlines()[-2])
        self.assertLessEqual(new_res, 100)
        self.assertGreaterEqual(new_res, 92)
        result = self.com_s1.execut_db_sql("select count(va) "
                                           "from (select "
                                           "get_instr_unique_sql() "
                                           "as va);")
        LOGGER.info(result)
        new_res = int(result.splitlines()[-2])
        self.assertLessEqual(new_res, 89)

    def tearDown(self):
        LOGGER.info("步骤4：恢复默认值")
        self.set_gs_guc("enable_auto_clean_unique_sql", "no")
        status = COMMONSH.restart_db_cluster()
        LOGGER.info(status)
        self.set_gs_guc("instr_unique_sql_count", "100", "reload")
        status = COMMONSH.get_db_cluster_status("detail")
        LOGGER.info(status)
        self.assertTrue("Normal" in status or "Degraded" in status)
        LOGGER.info(f'-----{os.path.basename(__file__)} end-----')